為了將 Google Calendar 與 MySQL 事件同步需要建立 Google Api 與本地 MySQL 資料庫
完整Code - https://github.com/su0625/google_calendar-mysql
Google Api
1.建立完 Google API 之後可以獲得 API 金鑰以及 OAuth 用戶端 ID
2.將 API 金鑰以及 OAuth 用戶端 ID,貼到 View/Calendar 底下的每份 Ejs 裡
MYSQL
1.建立名稱為 event 的資料表 欄位分別為 id,Date,Event,Event_id
網頁
JavaScript
新增事件(Google Calendar)
先透過getElementById
獲得新增事件所需的時間、事件名稱,並將值加入 resource,指定時區為Asia/Taipei
,再利用 Google Api 的insert 就可以將事件加入到 Calendar
function insertEvents() {
var start = document.getElementById("start").value;
var end = document.getElementById("end").value;
var summary=document.getElementById("summary").value;
// 判斷框框是否為空
if (start.length > 0 && end.length > 0 && summary.length>0){
var resource = {
"summary": summary,
"description": "",
"location": "Tapei",
"start": {
"dateTime": start+":00",
"timeZone":"Asia/Taipei"
},
"end": {
"dateTime": end+":00",
"timeZone":"Asia/Taipei"
}
};
var request = gapi.client.calendar.events.insert({
'calendarId': 'primary',
'resource': resource
});
request.execute(function(resp) {
console.log(resp);
});
alert("Added successfully")
}else{
alert("Please check your datetime and event.")
}
}
新增事件(MySQL)
當 Calendar 事件新增完後,透過選取事件範圍時間並按下List event 就可以查看事件是否成功加入,另外編輯刪除事件時都需要用到 Event Id,因此需要特別儲存起來
var array1 = []; //建立空陣列
var event_content = [];
var event_time=[];
var event_id =[]
if (events.length > 0) {
for (i = 0; i < events.length; i++) {
var event = events[i];
var when = event.start.dateTime;
var id = event.id
if (!when) {
when = event.start.date;
}
//將事件文字丟入陣列
array1.push('<br>'+event.summary + ' (' + when + ')')
event_content.push(event.summary)
event_time.push(when)
event_id.push(id)
}
document.getElementById("event_name").innerHTML = "Upcoming events:";
// 列出最近10筆event
document.getElementById("Sdate_name").innerHTML= array1 ;
document.getElementById("event_content").value= event_content ;
document.getElementById("event_content_time").value= event_time ;
document.getElementById("event_id").value= event_id ;
}
else{
document.getElementById("event_name").innerHTML = "No upcoming events";
}
再按下 SQL 按鈕觸發 Post,顯示出來的事件就會新增到本地 MySQL
app.post('/', function(req, res, next) {
search_text = req.body.searchText;
// 要新增的 Data
event_content = req.body.event_content
event_time = req.body.event_content_time
event_id = req.body.event_id
// sql 已有Data
sql_date = req.body.sql_date
sql_event = req.body.sql_event
sql_event_id = req.body.sql_event_id
console.log(event_content)
event_content = event_content.split(",");
event_time = event_time.split(",");
event_id = event_id.split(",");
// 檢查sql 是不是空的
if (typeof sql_event === "undefined") {
console.log("SQL Empty")
sql_event_id=["Empty"]
}
for (i=0; i< sql_event_id.length; i++){
if (sql_event_id.includes(event_id[i]+"</td")){
console.log("重複事件")
delete event_content[i]
delete event_time[i]
delete event_id[i]
}
else{
continue;
}
}
// 刪掉空值
var event_content = event_content.filter(el => el);
var event_time = event_time.filter(el => el);
var event_id = event_id.filter(el => el);
console.log("刪除完剩下",event_content,event_time,event_id)
req.getConnection(function(error, conn) {
for (i=0; i< event_content.length; i++) {
event_time[i] = event_time[i].replace('+08:00','')
var content = {
Date: event_time[i],
Event: event_content[i],
Event_id: event_id[i],
}
conn.query('INSERT INTO event SET ? ', content, function(err, result) {
if (err) {
console.log("err")
throw err
}
else {
req.flash('success', 'Data added successfully!')
}
})
}
res.redirect('/calendar')
})
})
顯示事件頁面
當按下 Edit 時,頁面跳轉,自動帶入原先事件名稱及開始時間
編輯事件(Google Calendar)
在 gapi 裡帶入需要編輯事件的event id,時間(start_date,end_date),事件名稱(event_content)
function calendar_Edit(id,event_id) {
var event_content = document.getElementById("event_name").value;
var start_date = document.getElementById("start_date").value;
var end_date = document.getElementById("end_date").value;
console.log(id,event_id,event_content)
console.log("start",start_date)
var request = gapi.client.calendar.events.update({
'calendarId': 'primary',
'eventId': event_id,
"resource": {
"end": {
"dateTime": end_date+":00",
"timeZone":"Asia/Taipei"
},
"start": {
"dateTime": start_date+":00",
"timeZone":"Asia/Taipei"
},
"summary": event_content
}
});
request.execute(function(resp) {
console.log(resp);
});
}
編輯事件(MySQL)
MySQL 的部分是透過 id 指定需要編輯的事件
app.get('/edit', function (req, res, next) {
var id = req.query.id;
console.log('Edit id',id)
req.getConnection(function(error, conn) {
conn.query('SELECT * FROM event WHERE id = ?', id, function (err, rows) {
if (err) {
console.log(err);
}
var data = rows;
res.render('calendar/edit', { title: 'Edit event', data: data });
});
});
})
刪除事件(Google Calendar)
在 gapi 裡帶入需要刪除事件的event id
function calendar_delete(id,event_id) {
var request = gapi.client.calendar.events.delete({
'calendarId': 'primary',
'eventId': event_id,
});
request.execute(function(resp) {
console.log(resp);
});
// 刪除sql event
window.location.href = "/calendar/delete?id=" + id;
}
刪除事件(MySQL)
MySQL 的部分是透過 id 指定需要刪除的事件
app.get('/delete', function (req, res, next) {
var id = req.query.id;
console.log(id)
req.getConnection(function(error, conn) {
conn.query('DELETE FROM event WHERE id = ?', id, function (err, rows) {
if (err) {
console.log(err);
}
res.redirect('/calendar/event');
});
});
})
Reference:
https://developers.google.com/calendar/api/v3/reference/events/insert